
/*
'===============================================================================
'  HNG.Portal.BL.TblSanPhamManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HNG.Portal.BLL
{
	public class TblSanPhamManager : TblSanPhamManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblSanPhamManager()
		{
			// Nothing for now.
		}

        public DataTable be_selectToGridView(TblSanPhamEntity _TblSanPhamEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[10];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblSanPhamEntity.SortExpression) ? "[ID]" : _TblSanPhamEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblSanPhamEntity.SortDirection) ? " DESC" : _TblSanPhamEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[TenSanPham],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[SPNoiBat],").Append("\n");
                sbSql.Append("  ch.[MaTienTe],").Append("\n");
                sbSql.Append("  ch.[DanhMucSPID],").Append("\n");
                sbSql.Append("  ch.[TrangThai],").Append("\n");
                sbSql.Append("  ch.[TenDanhMuc]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblSanPham ch").Append("\n");
                //sbSql.AppendFormat("  LEFT JOIN tblDanhMucSanPham dmt").Append("\n");
                //sbSql.Append("  ON dmt.ID = ch.DanhMucSPID").Append("\n");
                //sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                //sbSql.Append("  ON lng.ID = dmt.LangID").Append("\n");

                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblSanPhamEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");

                }
                string strCon = _TblSanPhamEntity.Condition;
                if (!string.IsNullOrEmpty(_TblSanPhamEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[TenSanPham] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TomTat] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Mota] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DanhMucSPID] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaBan] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaCu] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThoiGianBaoHanh] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[SoLuong] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMuc] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TenSanPham] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TomTat] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Mota] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DanhMucSPID] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaBan] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaCu] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThoiGianBaoHanh] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[SoLuong] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMuc] LIKE N'%{0}%'", strCon).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                //if (!string.IsNullOrEmpty(_TblSanPhamEntity.LangId))
                //{
                //    _SqlParameter[1] = new SqlParameter("@LangId", _TblSanPhamEntity.LangId);
                //    sbSql.Append("  AND dmt.[LangId] LIKE @LangId").Append("\n");
                //}

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.DM))
                {
                    _SqlParameter[2] = new SqlParameter("@DanhMucSPId", _TblSanPhamEntity.DM);
                    sbSql.Append("  AND ch.[DanhMucSPID] LIKE @DanhMucSPId OR ch.[DanhMucSPID] IN (SELECT ID FROM tblDanhMucSanPham WHERE DanhMucCha = @DanhMucSPId)").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.strDanhMucSanPham))
                {
                    _SqlParameter[2] = new SqlParameter("@strDanhMucSanPham", _TblSanPhamEntity.strDanhMucSanPham);
                    sbSql.Append("  AND ch.[DanhMucSPID] LIKE @strDanhMucSanPham").Append("\n");
                }


                //if (!string.IsNullOrEmpty(_TblSanPhamEntity.SpHot))
                //{
                //    _SqlParameter[3] = new SqlParameter("@TinHot", _TblSanPhamEntity.SpHot);
                //    sbSql.Append("  AND ch.[TinHot] LIKE @TinHot").Append("\n");
                //}

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.strSPNoiBat))
                {
                    _SqlParameter[4] = new SqlParameter("@SPNoiBat", _TblSanPhamEntity.strSPNoiBat);
                    sbSql.Append("  AND ch.[SPNoiBat] LIKE @SPNoiBat").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.Status))
                {
                    _SqlParameter[5] = new SqlParameter("@TrangThai", _TblSanPhamEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                if (System.DateTime.MinValue != _TblSanPhamEntity.CreateDateFrom)
                {
                    _SqlParameter[7] = new SqlParameter("@CreateDateFrom", _TblSanPhamEntity.CreateDateFrom);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayDang], @CreateDateFrom) < 0").Append("\n");
                }

                if (System.DateTime.MinValue != _TblSanPhamEntity.CreateDateTo)
                {
                    _SqlParameter[8] = new SqlParameter("@CreateDateTo", _TblSanPhamEntity.CreateDateTo);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayDang], @CreateDateTo) > 0").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.MaTienTe))
                {
                    _SqlParameter[8] = new SqlParameter("@MaTienTe", _TblSanPhamEntity.MaTienTe);
                    sbSql.Append("  AND ch.[MaTienTe] LIKE @MaTienTe").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblSanPhamEntity.StartIndex, _TblSanPhamEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_selectToDropdownlist(int CateID)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            try
            {
                sbSql.Append("SELECT ").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenSanPham]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblSanPham ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblDanhMucSanPham dmt").Append("\n");
                sbSql.Append("  ON dmt.ID = ch.DanhMucSPID").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = dmt.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(CateID.ToString()))
                {
                    _SqlParameter[0] = new SqlParameter("@DanhMucSPId", CateID);
                    sbSql.Append("  AND ch.[DanhMucSPID] LIKE @DanhMucSPId OR ch.[DanhMucSPID] IN (SELECT ID FROM tblDanhMucSanPham WHERE DanhMucCha = @DanhMucSPId)").Append("\n");
                }
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_selectSanPhamMuaKem(int ProID)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            try
            {
                sbSql.Append("SELECT ").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenSanPham]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblSanPham ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(ProID.ToString()))
                {
                    _SqlParameter[0] = new SqlParameter("@DanhMucSPId", ProID);
                    sbSql.Append("  AND ch.[ID] IN (SELECT IDsanphamkhuyenmai FROM tblSanPhamKhuyenMai WHERE IDsanpham=@DanhMucSPId)").Append("\n");
                }

            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_selectToAll(TblSanPhamEntity _TblSanPhamEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[9];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblSanPhamEntity.SortExpression) ? "[ID]" : _TblSanPhamEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblSanPhamEntity.SortDirection) ? " DESC" : _TblSanPhamEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[TenSanPham],").Append("\n");
                sbSql.Append("  ch.[TomTat],").Append("\n");
                sbSql.Append("  ch.[Mota],").Append("\n");
                sbSql.Append("  ch.[NhaCCID],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[GiaBan],").Append("\n");
                sbSql.Append("  ch.[GiaCu],").Append("\n");
                sbSql.Append("  ch.[ThoiGianBaoHanh],").Append("\n");
                sbSql.Append("  ch.[DonViBaoHanh],").Append("\n");
                sbSql.Append("  ch.[SoLuong],").Append("\n");
                sbSql.Append("  ch.[DonViTinhID],").Append("\n");
                sbSql.Append("  ch.[SPMoi],").Append("\n");
                sbSql.Append("  ch.[SPNoiBat],").Append("\n");
                sbSql.Append("  ch.[SpHot],").Append("\n");
                sbSql.Append("  ch.[NgayDang],").Append("\n");
                sbSql.Append("  ch.[TrangThai],").Append("\n");
                sbSql.Append("  dmt.[TenDanhMuc]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblSanPham ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblDanhMucSanPham dmt").Append("\n");
                sbSql.Append("  ON dmt.ID = ch.DanhMucTinID").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = dmt.LangID").Append("\n");

                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblSanPhamEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");

                }
                string strCon = _TblSanPhamEntity.Condition;
                if (!string.IsNullOrEmpty(_TblSanPhamEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[TenSanPham] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TomTat] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Mota] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DanhMucTinID] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaBan] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaCu] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThoiGianBaoHanh] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[SoLuong] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR dmt.[TenDanhMuc] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TenSanPham] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TomTat] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Mota] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DanhMucTinID] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaBan] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GiaCu] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThoiGianBaoHanh] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[SoLuong] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayDang] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR dmt.[TenDanhMuc] LIKE N'%{0}%'", strCon).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblSanPhamEntity.LangId);
                    sbSql.Append("  AND dmt.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.DM))
                {
                    _SqlParameter[2] = new SqlParameter("@DanhMucSPId", _TblSanPhamEntity.DM);
                    sbSql.Append("  AND ch.[DanhMucSPID] LIKE @DanhMucSPId OR ch.[DanhMucSPID] IN (SELECT ID FROM tblDanhMucSanPham WHERE DanhMucCha = @DanhMucSPId)").Append("\n");
                }

                //if (!string.IsNullOrEmpty(_TblSanPhamEntity.SpHot))
                //{
                //    _SqlParameter[3] = new SqlParameter("@TinHot", _TblSanPhamEntity.SpHot);
                //    sbSql.Append("  AND ch.[TinHot] LIKE @TinHot").Append("\n");
                //}

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.strSPNoiBat))
                {
                    _SqlParameter[4] = new SqlParameter("@SPNoiBat", _TblSanPhamEntity.strSPNoiBat);
                    sbSql.Append("  AND ch.[SPNoiBat] LIKE @SPNoiBat").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblSanPhamEntity.Status))
                {
                    _SqlParameter[5] = new SqlParameter("@TrangThai", _TblSanPhamEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                if (System.DateTime.MinValue != _TblSanPhamEntity.CreateDateFrom)
                {
                    _SqlParameter[7] = new SqlParameter("@CreateDateFrom", _TblSanPhamEntity.CreateDateFrom);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayDang], @CreateDateFrom) < 0").Append("\n");
                }

                if (System.DateTime.MinValue != _TblSanPhamEntity.CreateDateTo)
                {
                    _SqlParameter[8] = new SqlParameter("@CreateDateTo", _TblSanPhamEntity.CreateDateTo);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayDang], @CreateDateTo) > 0").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblSanPhamEntity.StartIndex, _TblSanPhamEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        /*--============================HOME==================================--*/
        /// <summary>
        /// lay 12 san pham noi bat hien thi ben ngoai trang chu
        /// </summary>
        /// <param name="_TblSanPhamEntity"></param>
        /// <returns></returns>
        public DataTable sp_Product_GetTop12ProductSpecial()
        {
            DataTable List = new DataTable();
            try
            {
                List = AE.SqlHelper.ExecuteDataTable(Conn, CommandType.StoredProcedure, "sp_Product_GetTop12ProductSpecial");
            }
            catch { }
            return List;
        }
        /// <summary>
        /// lay 6 san pham khuyen mai hien thi ben ngoai trang chu
        /// </summary>
        /// <param name="_TblSanPhamEntity"></param>
        /// <returns></returns>
        public DataTable sp_Product_GetTop6ProductKM()
        {
            DataTable List = new DataTable();
            try
            {
                List = AE.SqlHelper.ExecuteDataTable(Conn, CommandType.StoredProcedure, "sp_Product_GetTop6ProductKM");
            }
            catch { }
            return List;
        }
        /// <summary>
        /// lay 6 san pham moi nhati hien thi ben ngoai trang chu
        /// </summary>
        /// <param name="_TblSanPhamEntity"></param>
        /// <returns></returns>
        public DataTable sp_Product_GetTop6NewProduct()
        {
            DataTable List = new DataTable();
            try
            {
                List = AE.SqlHelper.ExecuteDataTable(Conn, CommandType.StoredProcedure, "sp_Product_GetTop6NewProduct");
            }
            catch { }
            return List;
        }
	}
}
